<?php
//by carboleda 20131024
class top10carteraModelo extends Modelo
{

	public function __construc(){
		parent::__construc();
	}

	public function cargarlista($lista, $filtro, $esMultiple) {
	    try {
	        $SQLSelectLista = "";
	        $sbReturn = "<option value='-1'>Seleccionar Valor</option>";

	        /*switch ($lista) {
	            case "almacen":
	                $SQLSelectLista = "select nosucursal as valor, nombre as etiqueta from sucursal ";
	                break;
                case "linea":
	                $SQLSelectLista = "select nolinea as valor, nombre as etiqueta from linea ";
	                break;
	            case "cliente":
	                $SQLSelectLista = "select nocliente as valor, cedula||' - '||nombre as etiqueta from cliente order by cedula";
	                break;
                case "departamento":
	                $SQLSelectLista = "select nodepartamento as valor, nombre as etiqueta from departamento ";
	                break;
                case "producto":
	                $SQLSelectLista = "select noproducto as valor, referencia||' - '||nombre as etiqueta from producto order by referencia";
	                break;
	            case "rutas":
	                $sbReturn = "";
	                $SQLSelectLista = " ";
	                break;            
	        }*/
	        $SQLSelectLista = "select valor, etiqueta from listas where lista = '".$lista."' ";

	        $bd_rc = $this->ejecutar( $SQLSelectLista );
			$no_rc = $this->contar( $bd_rc );

	        if ($no_rc > 0) {
	        	for( $i = 0; $i < $no_rc; $i++){
					$rg_rc = $this->buscaruno( $bd_rc, $i );

	                if($esMultiple === true) {
	                    $sbReturn.="<input type='checkbox' class='check".$lista."' value='" . trim($rg_rc['valor']) . "'>" . trim($rg_rc['valor']) . "</input>";
	                } else {
	                    $sbReturn.="<option value='" . trim($rg_rc['valor']) . "'>" . trim($rg_rc['etiqueta']) . "</option>";
	                }
	            }            
	            
	            if($esMultiple === true) {  
	                $sbReturn.="<input type='checkbox' value='todas' class='checkTodas'><strong>Todas</strong></input>";
	            }
	        }

	        return $sbReturn;
	    } catch (Exception $e) {
	        return null;
	    }
	}

	public function consultar_top10cartera($fecha_inicial, $fecha_final, $almacen, $linea, $cliente, $departamento, $producto){
		$lstResultado = array();
		$select = "punto_pagonombre";
		$where = "";
		
		if(isset($fecha_inicial) && isset($fecha_final)) {
			$where .= " AND facturafecha between '".$fecha_inicial."' AND '".$fecha_final."' ";			
		}
		if(isset($almacen) && $almacen !== "-1") {
			//$where .= " AND punto_pagonosucursal = ".$almacen." ";
			$select = "punto_pagonombre";
		}
		if(isset($linea) && $linea !== "-1") {
			//$where .= " AND productonolinea = ".$linea." ";
			$select = "lineanombre";
		}
		if(isset($cliente) && $cliente !== "-1") {
			//$where .= " AND facturanocliente = ".$cliente." ";
			$select = "clientecedula||' - '||clientenombre";
		}
		if(isset($departamento) && $departamento !== "-1") {
			//$where .= " AND ciudadnodepartamento = ".$departamento." ";
			$select = "departamentonombre";
		}
		if(isset($producto) && $producto !== "-1") {
			//$where .= " AND noproducto = ".$producto." ";
			$select = "productodescripcion";
		}

		$sql = "SELECT ".$select.",  
				TO_CHAR(ROUND(SUM(detalle_facturavalortotal), 2), '999,999,999.99') AS valor,
				SUM(detalle_facturavalortotal) as nvalor
				FROM top10cartera
				WHERE detalle_facturacantidad IS NOT NULL				
				" . $where . "
				GROUP BY ".$select." 
				ORDER BY nvalor DESC 
				LIMIT 10 OFFSET 0 ";

		//echo $sql;

		$bd_rc = $this->ejecutar( $sql );
		$no_rc = $this->contar( $bd_rc );

		if ($no_rc > 0) {			
        	for( $i = 0; $i < $no_rc; $i++){
				$rg_rc = $this->buscaruno( $bd_rc, $i );
				$lstResultado[] = $rg_rc;
            }
        }

		return $lstResultado;
	}
}

?>